package distributed.controller;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.sql.Date;

import distributed.entity.Event;
import distributed.entity.Choice;
import distributed.entity.Edge;

/**
 * Controller to handle communication with the MySQL database
 * @author Eric Finn
 *
 */
public class DataBaseController {
	private static DataBaseController instance;
	Connection conn;
	
	private class EventRecord {
		public String title;
		public Date creationDate;
		public int rounds;
		public int numLines;
	}

	/**
	 * Get the instance of the DataBaseController (creates one if the instance does not already exist)
	 * @return The instance of the DataBaseController
	 */
	public static DataBaseController getInstance() {
		if(instance == null) {
			instance = new DataBaseController();
		}
		return instance;
	}
	
	/**
	 * Connect to a database
	 * @param host The host that the database is running on ("mysql.wpi.edu")
	 * @param port The port to connect on ("3306")
	 * @param database The name of the database to connect to ("decision_lines")
	 * @param username The username to connect with ("objcontent")
	 * @param password The password to connect with ("bCPNQ4")
	 * @return true on success, false on failure
	 */
	public boolean connect(String host, String port, String database, String username, String password) {
		try {
			// The newInstance() call is a work around for some broken Java implementations
			//(From Connector/J documentation)
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		}
		catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
		
		try {
			conn = DriverManager.getConnection("jdbc:mysql://"+host+":"+port+"/"+database+"?user="+username+"&password="+password);
			return true;
		} catch (SQLException e) {
			// handle any errors
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			return false;
		}
	}
	
	/**
	 * Determine if a username/password combination are valid login credentials
	 * @param username Username of admin
	 * @param password Password of admin (this method performs no encryption)
	 * @return True on successful authentication, false on failed authentication
	 * @throws Exception
	 */
	public boolean authenticateAdmin(String username, String password) throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		boolean result = false;
		String safeUsername = escapeString(username);
		String safePassword = escapeString(password);
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM admin_accounts WHERE name='"+safeUsername+"' AND password='"+safePassword+"'");
			if(rs.next()) {
				result = true;
			}
			return result;
		}
		catch (SQLException ex){
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			throw new Exception("Unable to query for admin account with username "+username+" and password "+password);
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { } // ignore
				rs = null;
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) { } // ignore
				stmt = null;
			}
		}
	}
	
	/**
	 * Adds an admin account to the database
	 * @param username The username for the admin account
	 * @param password The password (already encrypted) for the admin account
	 * @return The number of rows affected (1 for successful addition of admin account, 0 otherwise)
	 */
	public int addAdminAccount(String username, String password) {
		String safeUsername = escapeString(username);
		String safePassword = escapeString(password);
		return sendUpdate("INSERT INTO admin_accounts (name, password) VALUES ('"+safeUsername+"', '"+safePassword+"')");
	}
	
	/**
	 * Delete an event in the database given an event id
	 * @param eventid The event ID of the event to delete
	 * @return The number of rows effected
	 * @throws Exception An Exception object with a message describing the error.
	 */
	public int deleteEvent(String eventid) throws Exception {
		if(!escapeString(eventid).equals(eventid)) {
			throw new Exception("Possible SQL injection detected with id='"+eventid+"'");
		}
		return sendUpdate("DELETE FROM events WHERE id='"+eventid+"'");
	}
	
	/**
	 * Get a list of event IDs for events older than a given date
	 * @param olderThan The date to compare events against
	 * @return The list of event IDs
	 * @throws Exception An exception with a message describing the error.
	 */
	public ArrayList<String> getIdsOfOldEvents(Date olderThan) throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM events WHERE creationDate < '"+olderThan.toString()+"'");
			ArrayList<String> result = new ArrayList<String>();
			while(rs.next()) {
				result.add(rs.getString("id"));
			}
			rs.close();
			return result;
		}
		catch (SQLException ex){
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			throw new Exception("Unable to get event IDs older than date='"+olderThan.toString()+"'");
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { } // ignore
				rs = null;
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) { } // ignore
				stmt = null;
			}
		}
	}
	
	/**
	 * Get an event that has been stored in the database
	 * @param id The ID generated for the event
	 * @return The event from the database, or null if no such event exists
	 * @throws Exception On error - the message will have more information as to how the error occurred.
	 */
	public Event retrieveEvent(String id) throws Exception {
		Event result = null;
		int numChoices = 0;
		
		if(!escapeString(id).equals(id)) {
			throw new Exception("Possible SQL injection detected with id='"+id+"'");
		}
		
		EventRecord eventRecord = getEvent(id);
		ArrayList<Choice> choices = getChoices(id);
		ArrayList<Edge> edges = getEdges(id);
				
		numChoices = choices.size();
		
		result = new Event(eventRecord.numLines, eventRecord.rounds, eventRecord.title, false);
		result.setCreationDate(eventRecord.creationDate);
		
		for(int choiceIdx = 0; choiceIdx < numChoices; choiceIdx++) {
			result.setChoice(choiceIdx, choices.get(choiceIdx).getChoice());
		}
		
		for(int edgeIdx = 0; edgeIdx < edges.size(); edgeIdx++) {
			result.addEdge(edges.get(edgeIdx));
		}
		
		return result;
	}
	
	/**
	 * Store an Event in the database
	 * @param toStore The Event to store
	 * @throws Exception on failure
	 */
	public void storeEvent(Event toStore, String id) {
		Choice[] choices = toStore.getLines();
		Edge[] edges = toStore.getEdges();
		int choicePos = 0;
		
		insertEvent(toStore, id);
		
		for(Choice currChoice : choices) {
			if(currChoice.getChoice() != null) {
				insertChoice(currChoice, choicePos, id);
				choicePos++;
			}
		}
		
		for(Edge currEdge : edges) {
			if(currEdge != null) {
				insertEdge(currEdge, id);
			}
		}
	}
	
	/**
	 * Update an event that already exists in the database.
	 * @param updatedEvent The Event object to update the database's version to
	 * @param id The id of the event to update
	 * @throws Exception An exception with a message describing the error.
	 */
	public void updateEvent(Event updatedEvent, String id) throws Exception {
		deleteEvent(id);
		storeEvent(updatedEvent, id);
	}
	
	/**
	 * Escape a string to prevent SQL injection
	 * @param toEscape The String to escape
	 * @return The escaped String
	 */
	String escapeString(String toEscape) {
		return toEscape.replaceAll("\"", "\\\"").replaceAll("'", "\\'");
	}
	
	/**
	 * Reverse an escape performed by escapeString
	 * @param toUnescape The String to unescape
	 * @return The unescaped String
	 */
	String unescapeString(String toUnescape) {
		return toUnescape.replaceAll("\\\"", "\"").replaceAll("\\'", "'");
	}
	
	EventRecord getEvent(String eventid) throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		EventRecord event = new EventRecord();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM events WHERE id='"+eventid+"'");
			if(!rs.next()) {
				rs.close();
				return null;
			}
			event.title = rs.getString("title");
			event.creationDate = rs.getDate("creationDate");
			event.rounds = rs.getInt("rounds");
			event.numLines = rs.getInt("numLines");
			return event;
		}
		catch (SQLException ex){
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			throw new Exception("Database Error: Unable to get event with eventid='"+eventid+"'");
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { } // ignore
				rs = null;
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) { } // ignore
				stmt = null;
			}
		}
	}
	
	ArrayList<Choice> getChoices(String eventid) throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM choices WHERE eventid='"+eventid+"' ORDER BY position ASC");
			ArrayList<Choice> result = new ArrayList<Choice>();
			while(rs.next()) {
				Choice thisChoice = new Choice(rs.getInt("position"));
				thisChoice.setChoice(unescapeString(rs.getString("value")));
				result.add(thisChoice);
			}
			rs.close();
			return result;
		}
		catch (SQLException ex){
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			throw new Exception("Database Error: Unable to get choices with eventid='"+eventid+"'");
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { } // ignore
				rs = null;
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) { } // ignore
				stmt = null;
			}
		}
	}
	
	ArrayList<Edge> getEdges(String eventid) throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM edges WHERE eventid='"+eventid+"'");
			ArrayList<Edge> result = new ArrayList<Edge>();
			while(rs.next()) {
				Edge thisEdge = new Edge(rs.getInt("leftchoice"), rs.getInt("height"));
				result.add(thisEdge);
			}
			rs.close();
			return result;
		}
		catch (SQLException ex){
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			throw new Exception("Unable to get edges with eventid='"+eventid+"'");
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) { } // ignore
				rs = null;
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) { } // ignore
				stmt = null;
			}
		}
	}
	
	/**
	 * Insert an Event object (but not its edges or choices) into the database and return the id generated for it
	 * @param toInsert Event to insert into the events table
	 * @return The number of rows inserted
	 * @throws Exception 
	 */
	int insertEvent(Event toInsert, String id) {
		return sendUpdate("INSERT INTO events (id, title, rounds, numLines, creationDate) VALUES ('"+id+"', '"+escapeString(toInsert.getQuestion())+"', "+toInsert.getRounds()+", "+toInsert.getLines().length+", '"+toInsert.getCreationDate().toString()+"')");
	}
	
	/**
	 * Insert a Choice object into the database
	 * @param toInsert The Choice object to insert
	 * @param position The position associated with the Choice object
	 * @param eventID The ID (in the database) of the Event object the Choice is associated with
	 * @return The number of rows inserted
	 */
	int insertChoice(Choice toInsert, int position, String id) {
		return sendUpdate("INSERT INTO choices (eventid, position, value) VALUES ('"+id+"', "+position+", '"+escapeString(toInsert.getChoice())+"')");
	}
	
	/**
	 * Insert an Edge object into the database
	 * @param toInsert The Edge object to insert
	 * @param eventID The ID (in the database) of the Event object the Edge is associated with
	 * @return The number of rows inserted
	 */
	int insertEdge(Edge toInsert, String id) {
		return sendUpdate("INSERT INTO edges (eventid, leftchoice, height) VALUES ('"+id+"', "+toInsert.getIndex()+", "+toInsert.getHeight()+")");
	}
	
	/**
	 * Send and execute an update query
	 * @param query The query to execute
	 * @return The number of rows affected by the update
	 */
	int sendUpdate(String query) {
		Statement stmt = null;
		int rows = 0;
		try {
			stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_UPDATABLE);
			rows = stmt.executeUpdate(query, Statement.NO_GENERATED_KEYS);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException ex) {
					// ignore
				}
			}
		}
		return rows;
	}
	
	private DataBaseController() {
		conn = null;
	}
}
